# mysql_handler.py
import os
import mysql.connector
from mysql.connector import Error
from configparser import ConfigParser
from typing import Dict, List, Tuple, Optional, Any


class MySQLConfig:
    """数据库配置类，支持读取多环境配置和表名前缀"""
    
    def __init__(self, config_path: str = "config.ini", section: str = "mysql_dev"):
        self.config = ConfigParser()
        self.section = section
        self.config_path = self._resolve_config_path(config_path)
        self._load_config()

    def _resolve_config_path(self, config_path: str) -> str:
        absolute_path = os.path.abspath(config_path)
        if not os.path.exists(absolute_path):
            raise FileNotFoundError(f"配置文件不存在：{absolute_path}")
        return absolute_path

    def _load_config(self) -> None:
        if not self.config.read(self.config_path, encoding="utf-8"):
            raise IOError(f"无法读取配置文件：{self.config_path}")
        
        if not self.config.has_section(self.section):
            raise ValueError(f"配置文件中不存在节点 {self.section}")
        
        self.host = self.config.get(self.section, "host", fallback="localhost")
        self.port = self.config.getint(self.section, "port", fallback=3306)
        self.user = self.config.get(self.section, "user", fallback="root")
        self.password = self.config.get(self.section, "password", fallback="")
        self.database = self.config.get(self.section, "database", fallback="")
        self.table_prefix = self.config.get(self.section, "table_prefix", fallback="").strip()

    def get_all_sections(self) -> List[str]:
        return self.config.sections()

    def switch_section(self, section: str) -> None:
        self.section = section
        self._load_config()

    def to_dict(self) -> Dict[str, Any]:
        return {
            "host": self.host,
            "port": self.port,
            "user": self.user,
            "password": self.password,
            "database": self.database
        }


class MySQLHandler:
    """数据库操作类，支持表名前缀自动拼接和CRUD操作"""
    
    def __init__(self, config: MySQLConfig):
        self.config = config
        self.connection: Optional[mysql.connector.connection.MySQLConnection] = None
        self.cursor: Optional[mysql.connector.cursor.MySQLCursor] = None
        self._current_table: Optional[str] = None  # 原始表名
        self._full_table: Optional[str] = None     # 带前缀的完整表名

    def set_table(self, table_name: str) -> None:
        """设置表名，自动添加配置中的前缀（修正后逻辑）"""
        self._current_table = table_name
        # 前缀与表名直接拼接（如tr_ + users → tr_users）
        self._full_table = f"{self.config.table_prefix}{table_name}" if self.config.table_prefix else table_name

    def _check_table_set(self) -> bool:
        if not self._full_table:
            print("错误：请先调用set_table方法设置表名")
            return False
        return True

    def connect(self) -> bool:
        self.disconnect()
        try:
            self.connection = mysql.connector.connect(**self.config.to_dict())
            if self.connection.is_connected():
                self.cursor = self.connection.cursor(dictionary=True)
                print(f"成功连接到数据库：{self.config.database}（节点：{self.config.section}）")
                return True
        except Error as e:
            print(f"数据库连接失败：{e}")
        return False

    def disconnect(self) -> None:
        if self.cursor:
            self.cursor.close()
            self.cursor = None
        if self.connection and self.connection.is_connected():
            self.connection.close()
            self.connection = None

    def switch_database(self, section: str) -> bool:
        try:
            self.config.switch_section(section)
            return self.connect()
        except Exception as e:
            print(f"切换数据库失败：{e}")
            return False

    def execute_query(self, query: str, params: Tuple[Any, ...] = ()) -> Optional[List[Dict[str, Any]]]:
        if not self.connection or not self.connection.is_connected():
            if not self.connect():
                return None

        try:
            self.cursor.execute(query, params)
            return self.cursor.fetchall()
        except Error as e:
            print(f"查询执行失败：{e}")
            return None

    def execute_update(self, query: str, params: Tuple[Any, ...] = ()) -> Optional[int]:
        if not self.connection or not self.connection.is_connected():
            if not self.connect():
                return None

        try:
            self.cursor.execute(query, params)
            self.connection.commit()
            return self.cursor.rowcount
        except Error as e:
            print(f"更新执行失败：{e}")
            self.connection.rollback()
            return None

    def insert_one(self, data: Dict[str, Any]) -> Optional[int]:
        if not self._check_table_set():
            return None
        
        columns = ', '.join(data.keys())
        placeholders = ', '.join(['%s'] * len(data))
        query = f"INSERT INTO {self._full_table} ({columns}) VALUES ({placeholders})"
        return self.execute_update(query, tuple(data.values()))

    def update(self, data: Dict[str, Any], condition: str, params: Tuple[Any, ...] = ()) -> Optional[int]:
        if not self._check_table_set():
            return None
        
        set_clause = ', '.join([f"{k} = %s" for k in data.keys()])
        query = f"UPDATE {self._full_table} SET {set_clause} WHERE {condition}"
        all_params = tuple(data.values()) + params
        return self.execute_update(query, all_params)

    def delete(self, condition: str, params: Tuple[Any, ...] = ()) -> Optional[int]:
        if not self._check_table_set():
            return None
        
        query = f"DELETE FROM {self._full_table} WHERE {condition}"
        return self.execute_update(query, params)

    def select(self, columns: str = '*', condition: str = '', 
               params: Tuple[Any, ...] = (), order_by: str = '', 
               limit: int = 0) -> Optional[List[Dict[str, Any]]]:
        if not self._check_table_set():
            return None
        
        query = f"SELECT {columns} FROM {self._full_table}"
        if condition:
            query += f" WHERE {condition}"
        if order_by:
            query += f" ORDER BY {order_by}"
        if limit > 0:
            query += f" LIMIT {limit}"
        return self.execute_query(query, params)
    
    def insert_or_update_many(self, data_list: List[Dict[str, Any]], unique_key: str) -> Optional[int]:
        """
        批量插入或更新记录
        
        Args:
            data_list: 待插入/更新的记录列表
            unique_key: 用于判断记录是否存在的唯一键字段名
            
        Returns:
            影响的行数，失败时返回None
        """
        if not self._check_table_set():
            return None
            
        if not data_list:
            print("警告：没有要处理的数据记录")
            return 0
            
        # 提取所有字段名（假设所有记录结构一致，以第一条为准）
        columns = data_list[0].keys()
        if unique_key not in columns:
            print(f"错误：唯一键 {unique_key} 不在数据字段中")
            return None
            
        # 构建字段列表和占位符
        columns_str = ', '.join(columns)
        placeholders = ', '.join(['%s'] * len(columns))
        
        # 构建更新字段（排除唯一键本身）
        update_columns = [f"{col} = VALUES({col})" for col in columns if col != unique_key]
        update_str = ', '.join(update_columns)
        
        # 构建完整SQL
        query = f"""
            INSERT INTO {self._full_table} ({columns_str}) 
            VALUES ({placeholders})
            ON DUPLICATE KEY UPDATE {update_str}
        """
        
        # 准备参数列表（将字典转换为有序元组）
        params = [tuple(item[col] for col in columns) for item in data_list]
        
        # 执行批量操作
        if not self.connection or not self.connection.is_connected():
            if not self.connect():
                return None
                
        try:
            self.cursor.executemany(query, params)
            self.connection.commit()
            return self.cursor.rowcount
        except Error as e:
            print(f"批量插入更新失败：{e}")
            self.connection.rollback()
            return None
    
    def start_transaction(self) -> bool:
        """开始事务（关闭自动提交）"""
        if not self.connection or not self.connection.is_connected():
            if not self.connect():
                return False
        
        try:
            self.connection.autocommit = False
            return True
        except Error as e:
            print(f"开始事务失败：{e}")
            return False

    def commit_transaction(self) -> bool:
        """提交事务（并恢复自动提交）"""
        if not self.connection or not self.connection.is_connected():
            print("错误：未建立数据库连接，无法提交事务")
            return False
        
        try:
            self.connection.commit()
            self.connection.autocommit = True  # 恢复自动提交模式
            return True
        except Error as e:
            print(f"提交事务失败：{e}")
            return False

    def rollback_transaction(self) -> bool:
        """回滚事务（并恢复自动提交）"""
        if not self.connection or not self.connection.is_connected():
            print("错误：未建立数据库连接，无法回滚事务")
            return False
        
        try:
            self.connection.rollback()
            self.connection.autocommit = True  # 恢复自动提交模式
            return True
        except Error as e:
            print(f"回滚事务失败：{e}")
            return False

    def __del__(self):
        self.disconnect()